﻿Imports System.Data.SqlClient

Public Class FrmCapNhatBanDo
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKho.DisplayMember = "TenKho"
        ComboBoxKho.ValueMember = "maKhoID"
        ComboBoxKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKe.DisplayMember = "TenKe"
        ComboBoxKe.ValueMember = "maKeID"
        ComboBoxKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDMHienTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHienTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHienTrang.DisplayMember = "TenHienTrang"
        ComboBoxHienTrang.ValueMember = "MaHienTrang"
        ComboBoxHienTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDMTyLe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTyLe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxTyLe.DisplayMember = "TenTyLe"
        ComboBoxTyLe.ValueMember = "MaTyle"
        ComboBoxTyLe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub LoatBanDoDiaChinhVaoTextBox()
        TextBoxMasoBD.DataBindings.Clear()
        TextBoxMasoBD.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "MaSoBD")
        TextBoxToBanDo.DataBindings.Clear()
        TextBoxToBanDo.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "tobandoso")
        TextBoxDiaDanh.DataBindings.Clear()
        TextBoxDiaDanh.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "Diadanh")
        DateTimeNgayNhap.DataBindings.Clear()
        DateTimeNgayNhap.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "NgayNhap")
        TextBoxTongToBanDo.DataBindings.Clear()
        TextBoxTongToBanDo.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TongToBanDo")
        TextBoxTongThua.DataBindings.Clear()
        TextBoxTongThua.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TongThua")
        TextBoxDienTich.DataBindings.Clear()
        TextBoxDienTich.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TongDienTich")
        TextBoxNamThanhLap.DataBindings.Clear()
        TextBoxNamThanhLap.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "NamThanhLap")
        TextBoxDonViDo.DataBindings.Clear()
        TextBoxDonViDo.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "DonViDo")
        ComboBoxTyLe.DataBindings.Clear()
        ComboBoxTyLe.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenTyle")
        ComboBoxHienTrang.DataBindings.Clear()
        ComboBoxHienTrang.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenHienTrang")
        ComboBoxLoaiTaiLieu.DataBindings.Clear()
        ComboBoxLoaiTaiLieu.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenLoaiTaiLieu")
        ComboBoxKe.DataBindings.Clear()
        ComboBoxKe.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenKe")
        ComboBoxKho.DataBindings.Clear()
        ComboBoxKho.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenKho")
    End Sub
    Public Sub NapDuLieuNhapVaoChinhSua()
        Dim NgayNhap As String
        NgayNhap = String.Format("{0:dd/MM/yyyy}", DateTimeNgayNhap.Value)
        DataGridViewChinhSua.Rows.Add(TextBoxMasoBD.Text, NgayNhap, TextBoxToBanDo.Text, TextBoxDiaDanh.Text, TextBoxNamThanhLap.Text, TextBoxDonViDo.Text, TextBoxTongThua.Text, TextBoxDienTich.Text)
    End Sub
    Private Sub NapDataGridViewBanDoDiaChinh()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
                                "   MasoBD " &
                                "  ,ngayNhap" &
                                "  ,TongToBanDo " &
                                "  ,TongThua " &
                                "  ,TongDienTich " &
                                "  ,NamThanhLap " &
                                "  ,DonViDo " &
                                "  ,DiaDanh " &
                                "  ,ToBanDoSo " &
                                "  ,TenTyLe " &
                                "  ,ht.TenHienTrang " &
                                "  ,tli.TenLoaiTaiLieu " &
                                "  ,ke.tenke " &
                                "  ,k.TenKho " &
                                " From  BanDoDiaChinh dc, DMTyLe tl, DMHienTrang ht, DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th " &
                                " WHERE dc.tyleid = tl.matyle " &
                                " and dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                " and dc.makhoid = k.makhoid " &
                                " and dc.maKeid = ke.makeid " &
                                " and MaHuyenId = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
                                " and dc.mahuyenid = th.maH " &
                                " and MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                " and dc.maxaid = tx.maxa " &
                                " and dc.maHienTrangID = ht.mahienTrang order by ngaynhap ", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBanDo.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub
    Private Sub CapNhatDulieu()
        Try
            Dim NgayNhap As String
            NgayNhap = String.Format("{0:MM/dd/yyyy}", DateTimeNgayNhap.Value)
            Dim Sql As String = ""
            Sql = " Update BanDoDiaChinh Set " &
                                "   NgayNhap = '" & NgayNhap & "' " &
                                "  ,TongToBanDo ='" & TextBoxTongToBanDo.Text & "' " &
                                "  ,TongThua = '" & TextBoxTongThua.Text & "' " &
                                "  ,TongDienTich = '" & TextBoxDienTich.Text & "' " &
                                "  ,NamThanhLap = '" & TextBoxNamThanhLap.Text & "' " &
                                "  ,DonViDo  =  N'" & TextBoxDonViDo.Text & "' " &
                                "  ,DiaDanh = N'" & TextBoxDiaDanh.Text & "'  " &
                                "  ,ToBanDoSo = '" & TextBoxToBanDo.Text & "' " &
                                "  ,TyLeID = '" & ComboBoxTyLe.SelectedValue.ToString & "' " &
                                "  ,MaHienTrangID = '" & ComboBoxHienTrang.SelectedValue.ToString & "' " &
                                "  ,MaLoaiTaiLieuID = '" & ComboBoxLoaiTaiLieu.SelectedValue.ToString & "'  " &
                                "  ,MakeID = '" & ComboBoxKe.SelectedValue.ToString & "' " &
                                "  ,MaKhoID = '" & ComboBoxKho.SelectedValue.ToString & "' " &
                                " where masoBD = '" & TextBoxMasoBD.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()

            MsgBox("Cập nhật thành công!")

            NapDataGridViewBanDoDiaChinh()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub FrmCapNhatBanDo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridViewBanDo.RowsDefaultCellStyle.BackColor = Color.LightBlue
        DataGridViewBanDo.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewChinhSua.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewChinhSua.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        NapDonViHuyen()
        NapKho()
        NapKe()
        NapDMHienTrang()
        NapLoaiTaiLieu()
        NapDMTyLe()
        NapDataGridViewBanDoDiaChinh()

    End Sub
    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub ComboBoxXa_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxXa.SelectedIndexChanged
        NapDataGridViewBanDoDiaChinh()
    End Sub

    Private Sub DataGridViewBanDo_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBanDo.CellContentClick
        LoatBanDoDiaChinhVaoTextBox()
    End Sub

    Private Sub DataGridViewBanDo_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBanDo.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBanDo.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBanDo.RowHeadersWidth < Size.Width + 20 Then DataGridViewBanDo.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()

    End Sub

    Private Sub ButtonNhap_Click(sender As Object, e As EventArgs) Handles ButtonNhap.Click
        CapNhatDulieu()
        NapDuLieuNhapVaoChinhSua()
    End Sub
    Private Sub DataGridViewChinhSua_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewChinhSua.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewChinhSua.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewChinhSua.RowHeadersWidth < Size.Width + 20 Then DataGridViewChinhSua.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
    Private Sub XoaDuLieu()
        If TextBoxMasoBD.Text = "" Then
            MessageBox.Show(" Chưa có thông tin để xóa !")
            Return
        Else
            Dim sql As String
            sql = "Delete from BanDoDiaChinh where masoBD= '" & TextBoxMasoBD.Text & " ' "
            Dim cmd As New SqlCommand(sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MessageBox.Show(" Bạn đã xóa thành công !")
        End If
    End Sub
    Private Sub XuatExcel()
        ' xuat tu datagrid sang excel
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "Ban do dia chinh"
                Dim i As Integer = 1
                For col = 0 To DataGridViewBanDo.Columns.Count - 1
                    .cells(1, i).value = DataGridViewBanDo.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewBanDo.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewBanDo.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewBanDo.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu xuất sang định dạng Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If TextBoxToBanDo.Text = "" Then
            MessageBox.Show("Bạn chưa chọn tờ bản đồ để xóa ")
            Return
        End If
        If MsgBox("Bạn muốn xóa dữ liệu không ? ", MsgBoxStyle.YesNo, "Thông báo ") = MsgBoxResult.Yes Then
            XoaDuLieu()
            NapDataGridViewBanDoDiaChinh()
            TextBoxToBanDo.Text = ""
            TextBoxDiaDanh.Text = ""
            TextBoxDienTich.Text = ""
            TextBoxDonViDo.Text = ""
            TextBoxMasoBD.Text = ""
            TextBoxNamThanhLap.Text = ""
            TextBoxTongThua.Text = ""
            TextBoxTongToBanDo.Text = ""
        Else
            Return
        End If
    End Sub

    Private Sub ButtonLuu_Click(sender As Object, e As EventArgs) Handles ButtonLuu.Click
        XuatExcel()
    End Sub

    Private Sub TextBoxMaID_TextChanged(sender As Object, e As EventArgs) Handles TextBoxMasoBD.TextChanged

    End Sub
End Class